﻿CREATE TRIGGER [dbo].[tr_Address_Zip]
ON [dbo].[t_Address]
FOR INSERT,UPDATE
AS

IF @@ROWCOUNT = 0 OR @@ROWCOUNT > 1 RETURN

IF TRIGGER_NESTLEVEL(@@PROCID) > 1 RETURN

SET NOCOUNT ON

IF UPDATE(Zip)
	UPDATE t_Address SET CityName = a.CityName,StateCode = a.StateCode
	FROM t_Address INNER JOIN
	(SELECT TOP (1)i.ID, Zips_USPS.CityName, Zips_USPS.StateCode
	 FROM inserted AS i
	 INNER JOIN Zips_USPS ON LTRIM(RTRIM(i.Zip)) = Zips_USPS.Zip
	)a ON t_Address.ID = a.ID
	--WHERE ISNULL(t_Address.CityName,'') = ''






